package com.qidian.hw.dao.impl;

import com.qidian.hw.dao.SolutionDAO;
import com.qidian.hw.pojo.Solution;
import com.qidian.hw.tools.FormatUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @Author ꒰ঌ(李ᆺ琛)໒꒱
 * @Date 2022/8/19 18:20
 * @Version 1.8
 */
public class SolutionDAOImpl extends BaseDAO implements SolutionDAO {
    private Connection con;
    private PreparedStatement pstmt;
    private ResultSet rs;

    //添加
    @Override
    public int saveSolution(Solution solution) {
        int result = 0;
        String sql = "insert into solution (solution_title,solution_desc,solution_img,solution_details,solution_type_id, " +
                " create_time,modify_time) values(?,?,?,?,?,?,?) ";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, solution.getSolutionTitle ());
            pstmt.setString (2, solution.getSolutionDesc ());
            pstmt.setString (3, solution.getSolutionImg ());
            pstmt.setString (4, solution.getSolutionDetails ());
            pstmt.setLong (5, solution.getSolutionTypeId ());
            String dateStr = FormatUtil.getNow ();//获取当前系统时间
            pstmt.setString (6, dateStr);
            pstmt.setString (7, dateStr);
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    //单个删除
    @Override
    public int deleteSolution(long solutionId) {
        int result = 0;
        String sql = "update solution set status=0,modify_time=? where solution_id = ?";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, FormatUtil.getNow ());
            pstmt.setLong (2, solutionId);
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    //更新
    @Override
    public int updateSolution(Solution solution) {
        int result = 0;
        String sql = "update solution set solution_title=?,solution_desc=?,solution_img=?,solution_details=?,solution_type_id=?, " +
                " modify_time=? where solution_id = ? and status = 1 ";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, solution.getSolutionTitle ());
            pstmt.setString (2, solution.getSolutionDesc ());
            pstmt.setString (3, solution.getSolutionImg ());
            pstmt.setString (4, solution.getSolutionDetails ());
            pstmt.setLong (5, solution.getSolutionTypeId ());
            pstmt.setString (6, FormatUtil.getNow ());
            pstmt.setLong (7, solution.getSolutionId ());
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    //查询全部，这个可以不写，通过queryByPage实现，写了就别删了
    @Override
    public ArrayList<Solution> listSolution() {
        ArrayList<Solution> solutions = new ArrayList<> ();
        String sql = "select * from solution where status = 1";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while (rs.next ()) {
                Solution solution = new Solution ();
                solution.setSolutionId (rs.getLong ("solution_id"));
                solution.setSolutionTitle (rs.getString ("solution_title"));
                solution.setSolutionDesc (rs.getString ("solution_desc"));
                solution.setSolutionImg (rs.getString ("solution_img"));
                solution.setSolutionDetails (rs.getString ("solution_details"));
                solution.setSolutionTypeId (rs.getLong ("solution_type_id"));
                solution.setCreateTime (rs.getString ("create_time"));
                solution.setModifyTime (rs.getString ("modify_time"));
                solutions.add (solution);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return solutions;
    }

    //通过id查询
    @Override
    public Solution querySolutionById(long solutionId) {
        Solution solution = null;
        String sql = "select * from solution where solution_id = ? and status = 1";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setLong (1, solutionId);
            rs = pstmt.executeQuery ();
            if (rs.next ()) {
                solution = new Solution ();
                solution.setSolutionId (rs.getLong ("solution_id"));
                solution.setSolutionTitle (rs.getString ("solution_title"));
                solution.setSolutionDesc (rs.getString ("solution_desc"));
                solution.setSolutionImg (rs.getString ("solution_img"));
                solution.setSolutionDetails (rs.getString ("solution_details"));
                solution.setSolutionTypeId (rs.getLong ("solution_type_id"));
                solution.setCreateTime (rs.getString ("create_time"));
                solution.setModifyTime (rs.getString ("modify_time"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return solution;
    }

    //统计满足查询条件的数据数量
    @Override
    public int count(String title, long solutionTypeId) {
        int result = 0;
        String sql = "select count(*) from solution where status = 1 ";
        if (FormatUtil.isNull (title)) {
            sql += " and solution_title like '%" + title + "%' ";
        }
        if (solutionTypeId > 0) {
            sql += " and solution_type_id = " + solutionTypeId;
        }
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            if (rs.next ()) {
                result = rs.getInt (1);
            }
        } catch (Exception e) {
            e.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }

    //分页查询
    @Override
    public ArrayList<Solution> queryByPage(int page, int size, String title, long solutionTypeId) {
        ArrayList<Solution> solutions = new ArrayList<Solution> ();
        String sql = "select * from solution where status = 1 ";
        if (FormatUtil.isNull (title)) {
            sql += " and solution_title like '%" + title + "%' ";
        }
        if (solutionTypeId > 0) {
            sql += " and solution_type_id = " + solutionTypeId;
        }
        sql += " limit " + ((page - 1) * size) + "," + size;
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while (rs.next ()) {
                Solution solution = new Solution ();
                solution.setSolutionId (rs.getLong ("solution_id"));
                solution.setSolutionTitle (rs.getString ("solution_title"));
                solution.setSolutionDesc (rs.getString ("solution_desc"));
                solution.setSolutionImg (rs.getString ("solution_img"));
                solution.setSolutionDetails (rs.getString ("solution_details"));
                solution.setSolutionTypeId (rs.getLong ("solution_type_id"));
                solution.setCreateTime (rs.getString ("create_time"));
                solution.setModifyTime (rs.getString ("modify_time"));
                solutions.add (solution);
            }
        } catch (Exception e) {
            e.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return solutions;
    }

    //批量删除
    @Override
    public int deleteBatch(String[] ids) {
        int result = 0;
        String sql = "update solution set status=0,modify_time=? where solution_id in (";
        for (int i = 0; i < ids.length; i++) {
            sql += ids[i] + ",";
        }
        sql += "0)";
        try {
            con = getCon ();
            pstmt = con.prepareStatement (sql);
            pstmt.setString (1, FormatUtil.getNow ());
            result = pstmt.executeUpdate ();
        } catch (SQLException throwables) {
            throwables.printStackTrace ();
        } finally {
            closeAll (rs, pstmt, con);
        }
        return result;
    }
}
